/**
 * Copyright 2019 吉鼎科技.
 *
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.easyplatform.support.scripting.cmd;

import cn.easyplatform.EasyPlatformWithLabelKeyException;
import cn.easyplatform.EntityNotFoundException;
import cn.easyplatform.contexts.Contexts;
import cn.easyplatform.contexts.RecordContext;
import cn.easyplatform.dao.BizDao;
import cn.easyplatform.dao.Page;
import cn.easyplatform.dos.FieldDo;
import cn.easyplatform.dos.Record;
import cn.easyplatform.entities.beans.batch.BatchBean;
import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.entities.beans.table.TableIndex;
import cn.easyplatform.interceptor.CommandContext;
import cn.easyplatform.lang.Lang;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.support.scripting.AbstractScriptCmd;
import cn.easyplatform.support.scripting.RhinoScriptable;
import cn.easyplatform.support.sql.SqlParser;
import cn.easyplatform.type.EntityType;
import cn.easyplatform.type.FieldType;
import cn.easyplatform.util.RuntimeUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @author <a href="mailto:davidchen@epclouds.com">littleDog</a> <br/>
 * @since 2.0.0 <br/>
 */
public class DbCmd extends AbstractScriptCmd {

    private List<DbHelper> helper;

    DbCmd(MainCmd cr) {
        super(cr);
    }

    public void init(String tableId, Object... keys) {
        TableBean tb = scc.getCommandContext().getEntity(tableId);
        if (tb == null)
            throw new EntityNotFoundException(EntityType.TABLE.getName(),
                    tableId);
        boolean isImport = StringUtils.equals(scc.getTarget()
                .getParameterAsString("759"), "IMPORT");
        if (helper == null) {
            helper = new ArrayList<DbCmd.DbHelper>();
            if (!isImport && scc.getSource() != null
                    && scc.getScope().getVariable("parent") == null) {
                MainCmd cmd = new MainCmd(scc.getScope(),
                        scc.getCommandContext(), scc.getWorkflowContext());
                cmd.setData(scc.getSource());
                scc.getScope().setVariable("parent", cmd);
            }
        }
        DbHelper info = new DbHelper();
        info.source = scc.getSource();
        info.target = scc.getTarget();
        info.tableId = scc.getTarget().getParameterAsString("833");
        helper.add(info);
        scc.setSource(scc.getTarget().copy());
        scc.setTarget(scc.getTarget().copy());
        Record record = null;
        if (keys == null || keys.length == 0)
            record = RuntimeUtils.createRecord(scc.getCommandContext(), tb,
                    tb.isAutoKey());
        else {
            if (keys.length != tb.getKey().size())
                throw new EasyPlatformWithLabelKeyException(
                        "table.key.size.invalid", tableId, Lang.concat(keys));
            record = RuntimeUtils.getTableRecord(scc.getCommandContext(), tb,
                    keys);
            if (record == null)
                throw new EasyPlatformWithLabelKeyException(
                        "table.record.not.found", tableId, Lang.concat(keys));
        }
        scc.getTarget().setParameter("833", tb.getId());
        scc.getTarget().setData(record);
        if (isImport) {
            MainCmd cmd = Contexts.get(MainCmd.class);
            if (cmd == null) {
                cmd = new MainCmd(scc.getScope(),
                        scc.getCommandContext(), scc.getWorkflowContext());
                cmd.setData(scc.getTarget());
                Contexts.set(MainCmd.class, cmd);
            }
            scc.getScope().setVariable("parent", cmd);
        }
    }

    public void execute(String code) {
        execute(code, false);
    }

    public boolean execute(String code, boolean isAutoCommit) {
        try {
            CommandContext cc = scc.getCommandContext();
            String table = scc.getTarget().getParameterAsString("833");
            TableBean tb = cc.getEntity(table);
            BizDao dao = cc.getBizDao(tb.getSubType());
            if (code == null || code.equalsIgnoreCase("C")) {
                return dao.insert(cc.getUser(), tb, scc.getTarget()
                        .getData(), isAutoCommit);
            } else if (code.equalsIgnoreCase("U"))
                return dao.update(cc.getUser(), tb.getId(), scc.getTarget()
                        .getData(), isAutoCommit);
            else if (code.equalsIgnoreCase("D")) {
                return dao.delete(cc.getUser(), tb.getId(), RuntimeUtils
                                .createPrimaryKey(tb, scc.getTarget().getData()),
                        isAutoCommit);
            } else if (code.equalsIgnoreCase("F")) {//先update，如果不存在insert
                boolean exists = false;
                if (tb.isAutoKey()) {
                    if (tb.getIndexes() != null && !tb.getIndexes().isEmpty()) {
                        TableIndex tableIndex = null;
                        for (TableIndex ti : tb.getIndexes()) {
                            if (ti.isUnique()) {
                                tableIndex = ti;
                                break;
                            }
                        }
                        if (tableIndex != null)
                            exists = update(scc.getTarget()
                                    .getData(), tb, tableIndex, dao, isAutoCommit);
                    } else
                        exists = dao.update(cc.getUser(), tb.getId(), scc.getTarget()
                                .getData(), isAutoCommit);
                } else
                    exists = dao.update(cc.getUser(), tb.getId(), scc.getTarget()
                            .getData(), isAutoCommit);
                if (!exists) {
                    exists = dao.insert(cc.getUser(), tb, scc.getTarget()
                            .getData(), isAutoCommit);
                }
                return exists;
            } else
                return false;
        } finally {
            if (helper != null && !helper.isEmpty()) {
                DbHelper info = helper.remove(helper.size() - 1);
                scc.setSource(info.source);
                scc.setTarget(info.target);
                scc.getTarget().setParameter("833", info.tableId);
                info = null;
            }
        }
    }

    public Object selectObject(String sql) {
        return selectObject(null, sql);
    }

    public Object selectObject(String dsId, String sql) {
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        FieldDo fd = scc.getCommandContext().getBizDao(dsId)
                .selectObject(sql, parameter);
        if (fd == null)
            return null;
        return fd.getValue();
    }

    public Object[] selectOne(String sql) {
        return selectOne(null, sql);
    }

    public Object[] selectOne(String dsId, String sql) {
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        FieldDo[] fields = scc.getCommandContext().getBizDao(dsId)
                .selectOne(sql, parameter);
        if (fields == null)
            return null;
        Object[] values = new Object[fields.length];
        for (int i = 0; i < values.length; i++)
            values[i] = fields[i].getValue();
        return values;
    }

    public Map<String, Object> selectDictionary(String sql, Object... parameter) {
        return selectDictionary0(null, sql, parameter);
    }

    public Map<String, Object> selectDictionary0(String dsId, String sql, Object... parameter) {
        if (parameter.length == 0) {
            SqlParser<Object> parser = RuntimeUtils.createSqlParser(Object.class);
            sql = parser.parse(sql, scc.getScope(), scc.getTarget());
            parameter = parser.getParams().toArray();
        }
        return scc.getCommandContext().getBizDao(dsId).selectDictionary(sql, parameter);
    }

    public Map<String, Object> selectMap(String sql, Object... parameter) {
        return selectMap0(null, sql, parameter);
    }

    public Map<String, Object> selectMap0(String dsId, String sql, Object... parameter) {
        if (parameter.length == 0) {
            SqlParser<Object> parser = RuntimeUtils.createSqlParser(Object.class);
            sql = parser.parse(sql, scc.getScope(), scc.getTarget());
            parameter = parser.getParams().toArray();
        }
        return scc.getCommandContext().getBizDao(dsId).selectMap(sql, parameter);
    }

    public List<Map<String, Object>> selectMapList(String sql, Object... parameter) {
        return selectMapList0(null, sql, parameter);
    }

    public List<Map<String, Object>> selectMapList0(String dsId, String sql, Object... parameter) {
        if (parameter.length == 0) {
            SqlParser<Object> parser = RuntimeUtils.createSqlParser(Object.class);
            sql = parser.parse(sql, scc.getScope(), scc.getTarget());
            parameter = parser.getParams().toArray();
        }
        return scc.getCommandContext().getBizDao(dsId).selectMapList(sql, null, parameter);
    }

    public List<Map<String, Object>> selectPageList(String sql, int pageNo, int pageSize, Object... parameter) {
        return selectPageList0(null, sql, pageNo, pageSize, parameter);
    }

    public List<Map<String, Object>> selectPageList0(String dsId, String sql, int pageNo, int pageSize, Object... parameter) {
        if (parameter.length == 0) {
            SqlParser<Object> parser = RuntimeUtils.createSqlParser(Object.class);
            sql = parser.parse(sql, scc.getScope(), scc.getTarget());
            parameter = parser.getParams().toArray();
        }
        return scc.getCommandContext().getBizDao(dsId).selectMapList(sql, new Page(pageNo, pageSize, false), parameter);
    }

    public Object selectList(String sql) {
        return selectList(null, sql);
    }

    public Object selectList(String dsId, String sql) {
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        List<FieldDo[]> fields = scc.getCommandContext().getBizDao(dsId)
                .selectList(sql, parameter);
        if (fields.isEmpty())
            return null;
        int size = fields.size();
        if (fields.get(0).length == 1) {// 如果查询的栏位只有一个，返回一维数组
            Object[] objects = new Object[size];
            for (int i = 0; i < size; i++) {
                FieldDo[] values = fields.get(i);
                objects[i] = values[0].getValue();
            }
            return objects;
        } else {
            Object[][] objects = new Object[size][fields.get(0).length];
            for (int i = 0; i < size; i++) {
                FieldDo[] values = fields.get(i);
                for (int j = 0; j < values.length; j++)
                    objects[i][j] = values[j].getValue();
            }
            return objects;
        }
    }

    public Object selectList(String sql, int pageNo, int pageSize,
                             String orderBy) {
        return selectList(null, sql, pageNo, pageSize, orderBy);
    }

    public Object selectList(String dsId, String sql, int pageNo,
                             int pageSize, String orderBy) {
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        Page page = new Page(pageSize);
        page.setPageNo(pageNo);
        page.setOrderBy(orderBy);
        page.setGetTotal(false);
        List<FieldDo[]> fields = scc.getCommandContext().getBizDao(dsId)
                .selectList(sql, parameter, page);
        if (fields.isEmpty())
            return null;
        int size = fields.size();
        if (fields.get(0).length == 1) {// 如果查询的栏位只有一个，返回一维数组
            Object[] objects = new Object[size];
            for (int i = 0; i < size; i++) {
                FieldDo[] values = fields.get(i);
                objects[i] = values[0].getValue();
            }
            return objects;
        } else {
            Object[][] objects = new Object[size][fields.get(0).length];
            for (int i = 0; i < size; i++) {
                FieldDo[] values = fields.get(i);
                for (int j = 0; j < values.length; j++)
                    objects[i][j] = values[j].getValue();
            }
            return objects;
        }
    }

    public void transfer() {
        transfer(null, null, false);
    }

    public void transfer(String tableId, String expr) {
        transfer(tableId, expr, false);
    }

    public void transfer(String tableId, String expr, boolean isAutoCommit) {
        if (Strings.isBlank(tableId) || Strings.isBlank(expr)) {
            if (scc.getSource() != null) {
                List<String> keys = null;
                if (scc.getTarget().getData() != null) {
                    keys = scc.getTarget().getData().getKey();
                    if (scc.getTarget().getData().getColumnCount() == scc
                            .getSource().getData().getColumnCount()) {
                        scc.getTarget().setData(
                                scc.getSource().getData().clone());
                    } else {
                        for (FieldDo field : scc.getTarget().getData()
                                .getData()) {
                            if (scc.getSource().getData().getColumnNames()
                                    .contains(field.getName())) {
                                field.setValue(scc.getSource().getFieldValue(
                                        field.getName()));
                            }
                        }
                    }
                } else
                    scc.getTarget().setData(scc.getSource().getData().clone());
                if (keys != null)
                    scc.getTarget().getData().setKey(keys);
            } else
                throw new EasyPlatformWithLabelKeyException(
                        "script.engine.cmd.transfer");
        } else {
            CommandContext cc = scc.getCommandContext();
            TableBean tb = cc.getEntity(tableId);
            if (tb == null)
                throw new EntityNotFoundException(EntityType.TABLE.getName(),
                        tableId);
            expr = expr.trim();
            if (expr.length() == 1) {//
                char c = expr.toUpperCase().charAt(0);
                boolean isAutoKey = tb.isAutoKey() && c == 'C';
                Record record = RuntimeUtils.createRecord(cc, tb, isAutoKey);
                Object key = null;
                if (isAutoKey)
                    key = record.get(tb.getKey().get(0)).getValue();
                for (FieldDo fd : record.getData()) {
                    FieldDo t = scc.getTarget().getData().get(fd.getName());
                    if (t != null)
                        fd.setValue(t.getValue());
                }
                if (isAutoKey)
                    record.get(tb.getKey().get(0)).setValue(key);
                BizDao dao = cc.getBizDao(tb.getSubType());
                if (c == 'C') {
                    dao.insert(cc.getUser(), tb, record, isAutoCommit);
                } else if (c == 'U')
                    dao.update(cc.getUser(), tb.getId(), record, isAutoCommit);
                else if (c == 'D')
                    dao.delete(cc.getUser(), tb.getId(),
                            RuntimeUtils.createPrimaryKey(tb, record),
                            isAutoCommit);
            } else {
                Record record = RuntimeUtils.createRecord(cc, tb, false);
                RecordContext tt = scc.getTarget().clone();
                tt.setData(record);
                RuntimeUtils.eval(cc, expr, scc.getTarget(), tt);
                char c = tt.getParameterAsChar("814");
                BizDao dao = cc.getBizDao(tb.getSubType());
                if (c == 'C') {
                    dao.insert(cc.getUser(), tb, record, isAutoCommit);
                } else if (c == 'U')
                    dao.update(cc.getUser(), tb.getId(), record, isAutoCommit);
                else if (c == 'D')
                    dao.delete(cc.getUser(), tb.getId(),
                            RuntimeUtils.createPrimaryKey(tb, record),
                            isAutoCommit);
            }
        }
    }

    public boolean selectMapping(String sql) {
        return selectMapping(null, sql);
    }

    public boolean selectMapping(String dsId, String sql) {
        boolean isCustom = sql.toLowerCase().startsWith("select");
        TableBean table = null;
        if (!isCustom) {
            StringBuilder sb = new StringBuilder("select ");
            table = scc.getCommandContext().getEntity(
                    scc.getTarget().getParameterAsString("833"));
            Iterator<TableField> itr = table.getFields().iterator();
            while (itr.hasNext()) {
                sb.append(itr.next().getName());
                if (itr.hasNext())
                    sb.append(",");
            }
            sb.append(" from ").append(table.getId()).append(" where ")
                    .append(sql);
            sql = sb.toString();
            sb = null;
        }
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        FieldDo[] fields = scc.getCommandContext().getBizDao(dsId)
                .selectOne(sql, parameter);
        if (fields == null)
            return false;
        if (isCustom) {
            Set<String> columnNames = scc.getTarget().getData()
                    .getColumnNames();
            for (FieldDo field : fields) {
                if (columnNames.contains(field.getName()))
                    scc.getTarget().setValue(field.getName(), field.getValue());
            }
        } else {
            scc.getTarget().setData(
                    RuntimeUtils.createRecord(scc.getCommandContext(), table,
                            false));
            for (FieldDo field : fields)
                scc.getTarget().setValue(field.getName(), field.getValue());
        }
        char code = scc.getTarget().getParameterAsChar("814");
        String tab = scc.getTarget().getParameterAsString("833");
        if (!Strings.isBlank(tab)
                && scc.getTarget().getParameterAsBoolean("815")
                && !scc.getTarget().getParameterAsString("830")
                .equals(BatchBean.class.getSimpleName())
                && (code == 'U' || code == 'D')) {
            if (table == null)
                table = scc.getCommandContext().getEntity(tab);
            scc.getCommandContext().getSync().lock(table, scc.getTarget());
        }
        return true;
    }

    public Object getEntity(String id) {
        return scc.getCommandContext().getEntity(id);
    }

    public void beginTx() {
        scc.getCommandContext().beginTx();
    }

    public void commitTx() {
        scc.getCommandContext().commitTx();
    }

    public void rollbackTx() {
        scc.getCommandContext().rollbackTx();
    }

    public void closeTx() {
        scc.getCommandContext().closeTx();
    }

    public void lock(String tableId, Object... keys) {
        scc.getCommandContext().getSync().lock(tableId, keys);
    }

    public void unlock(String tableId, Object... keys) {
        scc.getCommandContext().getSync().unlock(tableId, keys);
    }

    public int update(String sql) {
        return update(null, sql, false);
    }

    public int update(String dsId, String sql) {
        return update(dsId, sql, false);
    }

    public int update(String sql, boolean isAutoCommit) {
        return update(null, sql, isAutoCommit);
    }

    public int update(String dsId, String sql, boolean isAutoCommit) {
        SqlParser<FieldDo> sp = RuntimeUtils.createSqlParser(FieldDo.class);
        sql = sp.parse(sql, scc.getScope(), scc.getSource(), scc.getTarget());
        List<FieldDo> parameter = sp.getParams();
        CommandContext cc = scc.getCommandContext();
        return cc.getBizDao(dsId).update(cc.getUser(), sql, parameter,
                isAutoCommit);
    }

    public void update(RecordContext src, String tableId, String code,
                       String exp) {
        update(src, tableId, code, exp, false);
    }

    public void update(RecordContext src, String tableId, String code,
                       String exp, boolean isAutoCommit) {
        if (Strings.isBlank(code))
            throw new IllegalArgumentException("#db.update:code");
        CommandContext cc = scc.getCommandContext();
        TableBean tb = cc.getEntity(tableId);
        if (tb == null)
            throw new EntityNotFoundException(EntityType.TABLE.getName(),
                    tableId);
        Record record = RuntimeUtils.createRecord(cc, tb, false);
        RecordContext target = scc.getTarget().clone();
        target.setData(record);
        for (FieldDo field : record.getData()) {
            FieldDo fd = src.getData().get(field.getName());
            if (fd != null)
                field.setValue(fd.getValue());
        }
        if (!Strings.isBlank(exp))
            RuntimeUtils.eval(cc, exp, src, target);
        char c = code.charAt(0);
        BizDao dao = cc.getBizDao(tb.getSubType());
        if (c == 'C') {
            dao.insert(cc.getUser(), tb, record, isAutoCommit);
        } else if (c == 'U')
            dao.update(cc.getUser(), tb.getId(), record, isAutoCommit);
        else if (c == 'D')
            dao.delete(cc.getUser(), tb.getId(),
                    RuntimeUtils.createPrimaryKey(tb, record), isAutoCommit);
        else if (c == 'F') {
            boolean exists = false;
            if (tb.isAutoKey()) {
                if (tb.getIndexes() != null && !tb.getIndexes().isEmpty()) {
                    TableIndex tableIndex = null;
                    for (TableIndex ti : tb.getIndexes()) {
                        if (ti.isUnique()) {
                            tableIndex = ti;
                            break;
                        }
                    }
                    if (tableIndex != null)
                        exists = update(record, tb, tableIndex, dao, isAutoCommit);
                } else
                    exists = dao.update(cc.getUser(), tb.getId(), record, isAutoCommit);
            } else
                exists = dao.update(cc.getUser(), tb.getId(), record, isAutoCommit);
            if (!exists) {
                dao.insert(cc.getUser(), tb, record, isAutoCommit);
            }
        }
    }

    /**
     * 根据查询条件获取子结点
     *
     * @param query
     * @param key
     * @return
     */
    public Object[] getChildren(String query, Object key) {
        CommandContext cc = scc.getCommandContext();
        BizDao dao = cc.getBizDao();
        List<Object> data = new ArrayList<>();
        getChildren(dao, query, key, data);
        Object[] result = new Object[data.size()];
        data.toArray(result);
        data = null;
        return result;
    }

    private void getChildren(BizDao dao, String query, Object key, List<Object> result) {
        List<FieldDo> params = new ArrayList<FieldDo>();
        params.add(new FieldDo(FieldType.VARCHAR, key));
        List<FieldDo[]> data = dao.selectList(query, params);
        if (!data.isEmpty()) {
            for (FieldDo[] fvs : data) {
                Object val = fvs[0].getValue();
                if (!result.contains(val)) {// 避免死循环
                    result.add(val);
                    getChildren(dao, query, val, result);
                }//if
            }//for
        }//if
    }

    /**
     * 通过唯一索引更新记录
     *
     * @param record
     * @param tb
     * @param tableIndex
     * @param dao
     * @param isAutoCommit
     * @return
     */
    private boolean update(Record record, TableBean tb, TableIndex tableIndex, BizDao dao, boolean isAutoCommit) {
        //通过唯一索引来找记录
        CommandContext cc = scc.getCommandContext();
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ").append(tb.getId()).append(" SET ");
        List<FieldDo> params = new ArrayList<>();
        List<FieldDo> fields = new ArrayList<>();
        String[] idxs = tableIndex.getFields().split(",");
        for (FieldDo fd : record.getData()) {
            boolean inIndex = false;
            for (String name : idxs) {
                if (name.equalsIgnoreCase(fd.getName())) {
                    inIndex = true;
                    break;
                }
            }
            if (inIndex) {
                params.add(fd);
            } else {
                boolean inKey = false;
                if (tb.getKey() != null) {
                    for (String name : tb.getKey()) {
                        if (name.equalsIgnoreCase(fd.getName())) {
                            inKey = true;
                            break;
                        }
                    }
                }
                if (!inKey) {
                    fields.add(fd);
                    sb.append(fd.getName()).append("=?,");
                }
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" WHERE ");
        Iterator<FieldDo> itr = params.iterator();
        while (itr.hasNext()) {
            FieldDo fd = itr.next();
            sb.append(fd.getName()).append("=?");
            if (itr.hasNext())
                sb.append(" AND ");
        }
        fields.addAll(params);
        return dao.update(cc.getUser(), sb.toString(), fields, isAutoCommit) > 0;
    }

    private class DbHelper {

        RecordContext source;

        RecordContext target;

        String tableId;

    }

    //
    public SqlBuilder builder() {
        return new SqlBuilder();
    }


    public class SqlBuilder {

        int pageNo;

        int pageSize;

        long total = -1l;

        List<Map<String, String>> queryList = new ArrayList<>();

        public SqlBuilder total() {
            total = 0l;
            return this;
        }

        public SqlBuilder page(int pageNo, int pageSize) {
            this.pageNo = pageNo;
            this.pageSize = pageSize;
            return this;
        }

        public SqlBuilder query(String sql) {
            queryList.add(new HashMap<String, String>() {
                {
                    put("sql", sql);
                }
            });
            return this;
        }

        public SqlBuilder query(String dsId, String sql) {
            queryList.add(new HashMap<String, String>() {
                {
                    put("ds", dsId);
                    put("sql", sql);
                }
            });
            return this;
        }

        public SqlBuilder subQuery(String name, String sql) {
            return subQuery(null, name, sql);
        }

        public SqlBuilder subQuery(String dsId, String name, String sql) {
            if (queryList.isEmpty())
                throw new IllegalArgumentException("the query is no defined.");
            queryList.add(new HashMap<String, String>() {
                {
                    put("ds", dsId);
                    put("name", name);
                    put("sql", sql);
                }
            });
            return this;
        }

        public List<Map<String, Object>> execute() {
            if (queryList.isEmpty())
                throw new IllegalArgumentException("the query is no defined.");
            Map<String, String> main = queryList.remove(0);
            SqlParser<Object> parser = RuntimeUtils.createSqlParser(Object.class);
            String query = parser.parse(main.get("sql"), scc.getScope(), scc.getTarget());
            Object[] args = parser.getParams().toArray();
            List<Map<String, Object>> data;
            Page page = null;
            if (pageSize > 0)
                page = new Page(pageNo, pageSize, total == 0l);
            data = scc.getCommandContext().getBizDao(main.get("ds")).selectMapList(query, page, args);
            if (!queryList.isEmpty()) {
                RhinoScriptable scope = new RhinoScriptable();
                data.forEach(row -> {
                    queryList.forEach(map -> {
                        String dsId = map.get("ds");
                        String name = map.get("name");
                        String sql = map.get("sql");
                        scope.clear();
                        scope.putAll(row);
                        SqlParser<Object> sp = RuntimeUtils.createSqlParser(Object.class);
                        sql = sp.parse(sql, scope, scc.getTarget());
                        Object[] parameter = sp.getParams().toArray();
                        row.put(name, scc.getCommandContext().getBizDao(dsId).selectMapList(sql, null, parameter));
                    });
                });
            }
            if (total == 0l) {
                if (page == null)
                    total = data.size();
                else
                    total = page.getTotalCount();
            }
            return data;
        }

        public long getTotal() {
            return total;
        }
    }
}